﻿using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using StudentInfoSystem.Core.Data.DbContexts;
using StudentInfoSystem.Core.Interfaces;
using StudentInfoSystem.Core.Models;
using StudentInfoSystem.Core.Models.Enums;
using StudentInfoSystem.Core.Utils;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Text.Json;
using System.Threading.Tasks;

namespace StudentInfoSystem.Core.MockImplementations;

public class StudentService : IStudentService, IDisposable
{
    private readonly IServiceScopeFactory _scopeFactory;
    private readonly AppSettings _settings;
    private SearchCriteria? _currentSearchCriteria;
    private SortOption? _currentSortOption;
    private bool _isSortAscending = true;
    private bool _disposed = false;

    public StudentService(IServiceScopeFactory scopeFactory, AppSettings settings)
    {
        _scopeFactory = scopeFactory;
        _settings = settings;

        // 确保目录存在
        var directory = Path.GetDirectoryName(_settings.SavedDataBasePath);
        if (!string.IsNullOrEmpty(directory) && !Directory.Exists(directory))
        {
            Directory.CreateDirectory(directory);
        }

        // 初始化数据库
        InitializeDatabase();
    }

    private static readonly object lockObject = new();

    private static bool _isDatabaseInitialized = false; // 静态标志位
    private void InitializeDatabase()
    {
        if (_isDatabaseInitialized) return;

        lock (lockObject)
        {
            if (_isDatabaseInitialized) return;

            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            try
            {
                // 确保数据库连接正常
                context.Database.EnsureCreated();

                // 验证数据库是否创建成功
                var canConnect = context.Database.CanConnect();
                Debug.WriteLine($"数据库初始化: 连接状态 = {canConnect}");

                if (canConnect)
                {
                    // 检查是否有表存在
                    var tableExists = context.Database.ExecuteSqlRaw(
                        "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='Students'");
                    Debug.WriteLine($"Students表存在: {tableExists > 0}");
                }

                _isDatabaseInitialized = true;
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"数据库初始化失败: {ex.Message}");
                // 降级处理 - 使用内存数据库
                using var fallbackContext = new AppDbContext("Data Source=:memory:");
                fallbackContext.Database.EnsureCreated();
            }
        }
    }
    public void LoadStudents(string filePath)
    {
        ExecuteWithConcurrencyControl(() =>
        {
            try
            {
                // 1. 更新应用程序设置中的数据库路径
                _settings.SavedDataBasePath = filePath;

                // 2. 重置搜索和排序条件
                _currentSearchCriteria = null;
                _currentSortOption = null;
                _isSortAscending = true;

                // 3. 创建新数据库连接并验证
                using var scope = _scopeFactory.CreateScope();
                using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                context.Database.EnsureCreated();
            }
            catch (Exception ex)
            {
                throw new InvalidOperationException($"无法加载数据库文件: {ex.Message}");
            }
        });
    }
    public List<Student> GetAll()
    {
        return ExecuteWithPerformanceMonitoring(() =>
        {
            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            try
            {
                // 确保数据库连接正常
                if (!context.Database.CanConnect())
                {
                    Debug.WriteLine("无法连接到数据库");
                    return new List<Student>();
                }

                // 返回所有学生记录
                return context.Students.AsNoTracking().ToList();
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"获取学生数据时出错: {ex.Message}");
                return new List<Student>();
            }
        });
    }

    public Student? GetById(string studentId)
    {
        return ExecuteWithPerformanceMonitoring(() =>
        {
            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            return context.Students
                .AsNoTracking()
                .FirstOrDefault(s => s.Id == studentId);
        });
    }

    public void Add(Student student)
    {
        ExecuteWithConcurrencyControl(() =>
        {
            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            if (context.Students.Any(s => s.Id == student.Id))
                throw new InvalidOperationException("学号已存在");

            context.Students.Add(student);
            context.SaveChanges();
        });
    }

    public void Update(Student student)
    {
        ExecuteWithConcurrencyControl(() =>
        {
            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            var existing = context.Students
                .AsTracking()
                .FirstOrDefault(s => s.Id == student.Id);

            if (existing == null)
                throw new KeyNotFoundException("学生不存在");

            context.Entry(existing).CurrentValues.SetValues(student);
            context.SaveChanges();
        });
    }

    public void Delete(string studentId)
    {
        ExecuteWithConcurrencyControl(() =>
        {
            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            var student = context.Students
                .AsTracking()
                .FirstOrDefault(s => s.Id == studentId);

            if (student != null)
            {
                context.Students.Remove(student);
                context.SaveChanges();
            }
        });
    }

    public void Search(SearchCriteria criteria)
    {
        _currentSearchCriteria = criteria;
    }

    public void Sort(SortOption sortOption, bool isAscending)
    {
        _currentSortOption = sortOption;
        _isSortAscending = isAscending;
    }

    public IEnumerable<Student> GetAllFiltered()
    {
        return ExecuteWithPerformanceMonitoring(() =>
        {
            try
            {
                using var scope = _scopeFactory.CreateScope();
                using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                // 确保数据库连接正常
                if (!context.Database.CanConnect())
                {
                    Debug.WriteLine("无法连接到数据库");
                    return new List<Student>();
                }

                IQueryable<Student> query = context.Students.AsNoTracking().AsQueryable();

                // 应用搜索条件
                if (_currentSearchCriteria?.Field != null &&
                    !string.IsNullOrEmpty(_currentSearchCriteria.Keyword))
                {
                    query = _currentSearchCriteria.Field switch
                    {
                        SearchField.StudentId => query.Where(s => s.Id != null && s.Id.Contains(_currentSearchCriteria.Keyword)),
                        SearchField.Name => query.Where(s => s.Name != null && s.Name.Contains(_currentSearchCriteria.Keyword)),
                        SearchField.College => query.Where(s => s.College != null && s.College.Contains(_currentSearchCriteria.Keyword)),
                        SearchField.Department => query.Where(s => s.Department != null && s.Department.Contains(_currentSearchCriteria.Keyword)),
                        SearchField.Class => query.Where(s => s.Class != null && s.Class.Contains(_currentSearchCriteria.Keyword)),
                        SearchField.Dormitory => query.Where(s => s.Dormitory != null && s.Dormitory.Contains(_currentSearchCriteria.Keyword)),
                        SearchField.Hometown => query.Where(s => s.Hometown != null && s.Hometown.Contains(_currentSearchCriteria.Keyword)),
                        _ => query
                    };
                }

                // 应用排序条件
                if (_currentSortOption != null)
                {
                    query = (_currentSortOption, _isSortAscending) switch
                    {
                        (SortOption.ByStudentId, true) => query.OrderBy(s => s.Id),
                        (SortOption.ByStudentId, false) => query.OrderByDescending(s => s.Id),
                        (SortOption.ByCollege, true) => query.OrderBy(s => s.College),
                        (SortOption.ByCollege, false) => query.OrderByDescending(s => s.College),
                        (SortOption.ByDormitory, true) => query.OrderBy(s => s.Dormitory),
                        (SortOption.ByDormitory, false) => query.OrderByDescending(s => s.Dormitory),
                        (SortOption.ByDepartment, true) => query.OrderBy(s => s.Department),
                        (SortOption.ByDepartment, false) => query.OrderByDescending(s => s.Department),
                        (SortOption.ByClass, true) => query.OrderBy(s => s.Class),
                        (SortOption.ByClass, false) => query.OrderByDescending(s => s.Class),
                        _ => query
                    };
                }

                return query.ToList();
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"获取筛选学生数据时出错: {ex.Message}");
                // 记录完整异常信息
                Debug.WriteLine($"完整异常: {ex}");
                return new List<Student>();
            }
        });
    }

    public void SaveStudents()
    {
        // 由于每个操作都立即保存，此方法现在不需要做任何事情
        // 保留它只是为了接口兼容性
        Debug.WriteLine("SaveStudents called, but each operation already saves immediately");
    }

    public void ExportDatabase(string filePath, ExportFormat format)
    {
        ExecuteWithConcurrencyControl(() =>
        {
            try
            {
                using var scope = _scopeFactory.CreateScope();
                using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

                // 确保数据库连接正常
                if (!context.Database.CanConnect())
                {
                    throw new InvalidOperationException("无法连接到数据库");
                }

                switch (format)
                {
                    case ExportFormat.SQLite:
                        ExportToSQLite(context, filePath);
                        break;
                    case ExportFormat.SQLServer:
                        ExportToSQLServer(context, filePath);
                        break;
                    case ExportFormat.MySQL:
                        ExportToMySQL(context, filePath);
                        break;
                    case ExportFormat.CSV:
                        ExportToCSV(context, filePath);
                        break;
                    case ExportFormat.JSON:
                        ExportToJSON(context, filePath);
                        break;
                    default:
                        throw new NotSupportedException($"不支持的导出格式: {format}");
                }

                Debug.WriteLine($"数据库已成功导出为 {format} 格式: {filePath}");
            }
            catch (Exception ex)
            {
                Debug.WriteLine($"导出数据库失败: {ex.Message}");
                throw;
            }
        });
    }

    private void ExportToSQLite(AppDbContext sourceContext, string targetFilePath)
    {
        // 如果目标文件已存在，先删除
        if (File.Exists(targetFilePath))
        {
            File.Delete(targetFilePath);
        }

        // 使用SQLite的备份API复制数据库
        sourceContext.Database.ExecuteSqlRaw($"VACUUM INTO '{targetFilePath}'");
    }

    private void ExportToSQLServer(AppDbContext sourceContext, string targetFilePath)
    {
        // 获取所有学生数据
        var students = sourceContext.Students.AsNoTracking().ToList();

        // 生成SQL Server脚本
        var sqlScript = GenerateSQLServerScript(students);

        // 保存为SQL文件
        File.WriteAllText(targetFilePath, sqlScript);
    }

    private void ExportToMySQL(AppDbContext sourceContext, string targetFilePath)
    {
        // 获取所有学生数据
        var students = sourceContext.Students.AsNoTracking().ToList();

        // 生成MySQL脚本
        var sqlScript = GenerateMySQLScript(students);

        // 保存为SQL文件
        File.WriteAllText(targetFilePath, sqlScript);
    }

    private void ExportToCSV(AppDbContext sourceContext, string targetFilePath)
    {
        // 获取所有学生数据
        var students = sourceContext.Students.AsNoTracking().ToList();

        // 生成CSV内容
        var csvContent = GenerateCSVContent(students);

        // 保存为CSV文件
        File.WriteAllText(targetFilePath, csvContent, Encoding.UTF8);
    }

    private void ExportToJSON(AppDbContext sourceContext, string targetFilePath)
    {
        // 获取所有学生数据
        var students = sourceContext.Students.AsNoTracking().ToList();

        // 序列化为JSON
        var jsonContent = JsonSerializer.Serialize(students, new JsonSerializerOptions
        {
            WriteIndented = true,
            PropertyNamingPolicy = JsonNamingPolicy.CamelCase
        });

        // 保存为JSON文件
        File.WriteAllText(targetFilePath, jsonContent, Encoding.UTF8);
    }

    private string GenerateSQLServerScript(List<Student> students)
    {
        var sb = new StringBuilder();

        // 添加表创建语句
        sb.AppendLine("IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='Students' AND xtype='U')");
        sb.AppendLine("CREATE TABLE Students (");
        sb.AppendLine("    Id NVARCHAR(8) PRIMARY KEY,");
        sb.AppendLine("    Name NVARCHAR(50) NOT NULL,");
        sb.AppendLine("    College NVARCHAR(100),");
        sb.AppendLine("    Department NVARCHAR(100),");
        sb.AppendLine("    Class NVARCHAR(50),");
        sb.AppendLine("    Dormitory NVARCHAR(50),");
        sb.AppendLine("    Hometown NVARCHAR(100),");
        sb.AppendLine("    Phone NVARCHAR(20)");
        sb.AppendLine(");");
        sb.AppendLine();

        // 添加插入语句
        foreach (var student in students)
        {
            sb.AppendLine("INSERT INTO Students (Id, Name, College, Department, Class, Dormitory, Hometown, Phone, CreatedAt, UpdatedAt)");
            sb.AppendLine($"VALUES ('{EscapeSqlValue(student.Id)}', '{EscapeSqlValue(student.Name)}', '{EscapeSqlValue(student.College)}',");
            sb.AppendLine($"        '{EscapeSqlValue(student.Department)}', '{EscapeSqlValue(student.Class)}', '{EscapeSqlValue(student.Dormitory)}',");
            sb.AppendLine($"        '{EscapeSqlValue(student.Hometown)}', '{EscapeSqlValue(student.Phone)}'");
            sb.AppendLine();
        }

        return sb.ToString();
    }

    private string GenerateMySQLScript(List<Student> students)
    {
        var sb = new StringBuilder();

        // 添加表创建语句
        sb.AppendLine("CREATE TABLE IF NOT EXISTS Students (");
        sb.AppendLine("    Id VARCHAR(8) PRIMARY KEY,");
        sb.AppendLine("    Name VARCHAR(50) NOT NULL,");
        sb.AppendLine("    College VARCHAR(100),");
        sb.AppendLine("    Department VARCHAR(100),");
        sb.AppendLine("    Class VARCHAR(50),");
        sb.AppendLine("    Dormitory VARCHAR(50),");
        sb.AppendLine("    Hometown VARCHAR(100),");
        sb.AppendLine("    Phone VARCHAR(20),");
        sb.AppendLine("    CreatedAt DATETIME,");
        sb.AppendLine("    UpdatedAt DATETIME");
        sb.AppendLine(");");
        sb.AppendLine();

        // 添加插入语句
        foreach (var student in students)
        {
            sb.AppendLine("INSERT INTO Students (Id, Name, College, Department, Class, Dormitory, Hometown, Phone, CreatedAt, UpdatedAt)");
            sb.AppendLine($"VALUES ('{EscapeSqlValue(student.Id)}', '{EscapeSqlValue(student.Name)}', '{EscapeSqlValue(student.College)}',");
            sb.AppendLine($"        '{EscapeSqlValue(student.Department)}', '{EscapeSqlValue(student.Class)}', '{EscapeSqlValue(student.Dormitory)}',");
            sb.AppendLine($"        '{EscapeSqlValue(student.Hometown)}', '{EscapeSqlValue(student.Phone)}'");
            sb.AppendLine();
        }

        return sb.ToString();
    }

    private string GenerateCSVContent(List<Student> students)
    {
        var sb = new StringBuilder();

        // 添加标题行
        sb.AppendLine("学号,姓名,学院,系,班级,宿舍,籍贯,电话,创建时间,更新时间");

        // 添加数据行
        foreach (var student in students)
        {
            sb.AppendLine($"\"{EscapeCsvValue(student.Id)}\",\"{EscapeCsvValue(student.Name)}\",\"{EscapeCsvValue(student.College)}\",");
            sb.AppendLine($"\"{EscapeCsvValue(student.Department)}\",\"{EscapeCsvValue(student.Class)}\",\"{EscapeCsvValue(student.Dormitory)}\",");
            sb.AppendLine($"\"{EscapeCsvValue(student.Hometown)}\",\"{EscapeCsvValue(student.Phone)}\"");
        }

        return sb.ToString();
    }

    private string EscapeSqlValue(string value)
    {
        if (string.IsNullOrEmpty(value)) return "";
        return value.Replace("'", "''");
    }

    private string EscapeCsvValue(string value)
    {
        if (string.IsNullOrEmpty(value)) return "";
        return value.Replace("\"", "\"\"");
    }

    public void DeleteStudents(string filePath)
    {
        ExecuteWithConcurrencyControl(() =>
        {
            using var scope = _scopeFactory.CreateScope();
            using var context = scope.ServiceProvider.GetRequiredService<AppDbContext>();

            context.Students.ExecuteDelete();
            context.SaveChanges();
        });
    }

    #region 线程安全与性能优化
    private T ExecuteWithPerformanceMonitoring<T>(Func<T> operation)
    {
        var watch = System.Diagnostics.Stopwatch.StartNew();
        try
        {
            return operation();
        }
        finally
        {
            watch.Stop();
            // 实际项目中可添加性能日志记录
            // Debug.WriteLine($"操作耗时: {watch.ElapsedMilliseconds}ms");
        }
    }

    private void ExecuteWithConcurrencyControl(Action operation)
    {
        // 使用简单的锁机制确保线程安全
        lock (this)
        {
            operation();
        }
    }
    #endregion

    #region IDisposable实现
    protected virtual void Dispose(bool disposing)
    {
        if (!_disposed)
        {
            if (disposing)
            {
                // 不再需要手动处理DbContext，因为每个操作都创建新的实例
            }
            _disposed = true;
        }
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(this);
    }
    #endregion
}